--    Author    : ChenErHao
--    Name      : EDW.DEPB_INFO.HQL
--    Functions : 
--    Purpose   : Daily saving the variation data from stg
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-03-09  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE EDW.DEPB_INFO PARTITION (DATA_DATE = '#V_DATA_DATE#',DATA_SRC_ORG) 
SELECT /*+ MAPJOIN(D) */
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE             -- 跑批日期
   ,DATA_ORG_CODE                                                                   -- 数据机构
   ,CUSTOMER_TYPE                                                                   -- 客户类型
   ,ACCOUNT_CODE                                                                    -- 存款账户代码
   ,DEP_AGREEMENT_CODE                                                              -- 存款协议代码
   ,PRODUCT_CATEGORY                                                                -- 产品类别
   ,DEP_AGREEMENT_SDATE                                                             -- 存款协议起始日
   ,DEP_AGREEMENT_EDATE                                                             -- 存款协议到期日
   ,DEP_CUR_TYPE                                                                    -- 存款币种
   ,DEP_BALANCE                                                                     -- 存款余额
   ,RATE_FIXED_FLOAT                                                                -- 利率是否固定
   ,RATE_LEVEL                                                                      -- 利率水平
   ,CASE WHEN RATE_LEVEL<=0.35 THEN 10
         WHEN RATE_LEVEL<=1    THEN 20
         WHEN RATE_LEVEL<=1.5  THEN 30
         WHEN RATE_LEVEL<=2    THEN 40
         WHEN RATE_LEVEL<=2.5  THEN 50
         WHEN RATE_LEVEL<=3    THEN 60
         WHEN RATE_LEVEL<=3.5  THEN 70
         WHEN RATE_LEVEL<=4    THEN 80
         WHEN RATE_LEVEL<=5    THEN 90
         WHEN RATE_LEVEL > 5   THEN 91  
    END RATE_LEVEL_QJ                                                               --利率区间
   ,CASE WHEN PRODUCT_CATEGORY IN ('D011','D013','D051')
           THEN  INTEREST_LIVE
        WHEN DEP_AGREEMENT_EDATE IS NULL OR DEP_AGREEMENT_EDATE = ''
          THEN 0
        WHEN PRODUCT_CATEGORY='D03' AND DEP_AGREEMENT_EDATE='1999-01-01'
          THEN MESSAGE_ONED
        WHEN PRODUCT_CATEGORY='D03' AND DEP_AGREEMENT_EDATE='1999-01-07'
          THEN MESSAGE_SEVEND
        WHEN PRODUCT_CATEGORY='D052' 
          THEN TREATY_DEPOSIT
        WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<0
          THEN 0
        WHEN PRODUCT_CATEGORY  IN ('D012','D014','D0141')
            AND CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=3
          THEN  FULL_THREEM
        WHEN PRODUCT_CATEGORY  IN ('D012','D014','D0141')
            AND CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=6
          THEN FULL_SIXM
        WHEN PRODUCT_CATEGORY  IN ('D012','D014','D0141')
            AND CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=12
          THEN  FULL_ONEY
        WHEN PRODUCT_CATEGORY  IN ('D012','D014','D0141')
            AND CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=24
          THEN  FULL_TWOY
        WHEN PRODUCT_CATEGORY  IN ('D012','D014','D0141')
            AND CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=36
          THEN  FULL_THREEY
        WHEN PRODUCT_CATEGORY  IN ('D012','D014','D0141')
            AND CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=60
          THEN  FULL_FIVEY
        WHEN PRODUCT_CATEGORY  IN ('D0142','D0143','D0145')
            AND CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=12
          THEN  DIFF_ONEY
        WHEN PRODUCT_CATEGORY  IN ('D0142','D0143','D0145')
          AND CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=36
          THEN  DIFF_THREEY
        WHEN PRODUCT_CATEGORY  IN ('D0142','D0143','D0145')
            AND CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=60
          THEN  DIFF_FIVEY
       ELSE 0
    END BASE_RATE                                                                           -- 基准利率
   ,CASE WHEN product_category in ('D02','D011','D013','D03') 
         OR DEP_AGREEMENT_SDATE='' OR DEP_AGREEMENT_SDATE IS NULL 
         OR DEP_AGREEMENT_EDATE='' OR DEP_AGREEMENT_EDATE IS NULL THEN 9999 
       WHEN LENGTH(DEP_AGREEMENT_SDATE)<10 OR LENGTH(DEP_AGREEMENT_EDATE)<10 THEN '9999'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=6  THEN 'A'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=12 THEN 'B'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=18 THEN 'C1'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=24 THEN 'C2'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=30 THEN 'C3'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=36 THEN 'C4'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=42 THEN 'D1'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=48 THEN 'D2'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=54 THEN 'D3'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=60 THEN 'D4'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=66  THEN 'E1'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=72 THEN 'E2'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=78 THEN 'E3'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=84 THEN 'E4'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=90 THEN 'E5'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=96 THEN 'E6'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=102 THEN 'E7'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=108 THEN 'E8'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=114 THEN 'E9'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))<=120 THEN 'E91'
       WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,DEP_AGREEMENT_SDATE))>120 THEN 'F1' 
       ELSE 9999
    END AS LOAN_PERIOD_TYPE_ID                                                                        -- 期限区间
   ,CASE WHEN product_category = 'D03' OR DEP_AGREEMENT_EDATE='' OR DEP_AGREEMENT_EDATE IS NULL THEN 9999
         WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,'#V_LINE_DATE#'))<=6  THEN 'A'
         WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,'#V_LINE_DATE#'))<=12 THEN 'B'
         WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,'#V_LINE_DATE#'))<=36 THEN 'C'
         WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,'#V_LINE_DATE#'))<=60 THEN 'D'
         WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,'#V_LINE_DATE#'))<=120 THEN 'E'
         WHEN CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,'#V_LINE_DATE#'))>120 THEN 'F'
         ELSE 9999 
    END AS SURPLUS_PERIOD_TYPE_ID                                                                      --剩余期限区间
   ,(CASE WHEN DEP_AGREEMENT_EDATE='' OR DEP_AGREEMENT_EDATE IS NULL THEN 0 
        ELSE CEIL(MONTHS_BETWEEN(DEP_AGREEMENT_EDATE,'#V_LINE_DATE#')) 
    END) * DEP_BALANCE AS JQ_SURPLUS_PERIOD_TYPE_ID                                                   --加权剩余期限
   ,T3.AREA_NO_ID_1 AS BELONG_PROV_CODE                                                               -- 省
   ,T3.AREA_NO_ID AS BELONG_AREA_CODE                                                                 -- 市
   ,T3.AREA_NO_ID AS BELONG_CITY_CODE                                                                 -- 县
   ,T2.JRTJ_AREA_CODE                                                                                 -- 大集中地区编码
   ,CASE WHEN T2.ORG_LVL_ID_2='A05' THEN 'A05'
     ELSE T2.ORG_LVL_ID_3
    END AS DATA_ORG_TYPE                                                                              -- 数据机构类型
   ,DEP_BALANCE*RATE_LEVEL AS LOAN_OCCURRENCE_LEVEL                                                   -- 金额加权值
   ,CASE WHEN DEP_BALANCE/10000<0 THEN '9999'
        WHEN DEP_BALANCE/10000<=5 THEN 'A1'
        WHEN DEP_BALANCE/10000<=10 THEN 'A2'
        WHEN DEP_BALANCE/10000<=30 THEN 'A3'
        WHEN DEP_BALANCE/10000<=50 THEN 'A4'
        WHEN DEP_BALANCE/10000<=100 THEN 'B1'
        WHEN DEP_BALANCE/10000<=300 THEN 'B2'
        WHEN DEP_BALANCE/10000<=500 THEN 'B3'
        WHEN DEP_BALANCE/10000<=800 THEN 'C1'
        WHEN DEP_BALANCE/10000<=1000 THEN 'C2'
        WHEN DEP_BALANCE/10000<=5000 THEN 'C3'
        WHEN DEP_BALANCE/10000<=10000 THEN 'D'
        WHEN DEP_BALANCE/10000<=100000 THEN 'E'
        WHEN DEP_BALANCE/10000>100000 THEN 'F'
      ELSE '9999'
     END AS AMT_INTERVAL_ID                                                                          -- 金额区间
   ,1 AS STROKE_COUNT                                                                                  -- 笔数
   ,DATA_SRC_ORG                                                                                  -- 上报机构
 FROM ODS.DEPB T1
  LEFT JOIN DMCODE.T_ORG_BIZ_LVL T2 ON T1.DATA_DATE='#V_DATA_DATE#' AND T1.DATA_ORG_CODE=t2.ORG_ID
  LEFT JOIN DMCODE.T_AREA_CODE T3 ON T1.DATA_DATE = '#V_DATA_DATE#' AND T2.AREA_NO_ID = T3.AREA_NO_ID
  JOIN DMCODE.T_DEPB_RATE D
WHERE  DATA_DATE ='#V_DATA_DATE#'
AND  NVL(T1.DEP_AGREEMENT_SDATE,'10000101') BETWEEN D.BEGIN_DATE AND D.END_DATE
;